package useBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import JDBC.JDBC;
import javaBean.Message;
import javaBean.User;
public class UserDB {


	//插入用户
	public static int insert(User user) throws Exception {

		Connection connection = JDBC.getConnection();
		PreparedStatement ps = null;

		String query = "INSERT INTO user (user_name, password) VALUES (?, ?)";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, user.getusername());
			ps.setString(2, user.getpassword());
			return ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			return 0;
		} finally {
			JDBC.release(ps,connection);
		}
	}


	//判断用户名是否存在
	public static boolean UserExists(String username) throws Exception {
		Connection connection = JDBC.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "SELECT * FROM user  WHERE user_name = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, username);
			rs = ps.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			JDBC.release(ps, rs, connection);
		}
	}

	//根据username读出user
	public static User selectUser(String username) throws Exception {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String query = "SELECT * FROM user WHERE user_name = ?";

		try {
			connection = JDBC.getConnection();
			ps = connection.prepareStatement(query);
			ps.setString(1, username);
			rs = ps.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User ();
				user.setid(rs.getString("id"));
				user.setusername(rs.getString("user_name"));
				user.setpassword(rs.getString("password"));
			}
			return user;
		} catch (Exception e) {
			System.out.println(e);
			return  null ;
		} finally {
			JDBC.release(ps,rs,connection);
		}
	}




	//根据userid读出user
		public static User selectUser1(String id) throws Exception {
			Connection connection = JDBC.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;


			String query = "SELECT * FROM user WHERE id = ?";
			try {
				ps = connection.prepareStatement(query);
				ps.setString(1, id);
				rs = ps.executeQuery();
				User user = null;
				if (rs.next()) {
					user = new User ();
					user.setid(rs.getString("id"));
					user.setusername(rs.getString("user_name"));
					user.setpassword(rs.getString("password"));
				}
				return user;
			} catch (SQLException e) {
				System.out.println(e);
				return null;
			} finally {
				JDBC.release(ps,rs,connection);
			}
		}
	
		
	//从message表中读出所有数据
	public static List<Message> selectU() throws Exception {
		List<Message> mL= new ArrayList<Message>();
		Connection connection = JDBC.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		
		String query = "SELECT * FROM Message ";
		try {
			ps = connection.prepareStatement(query);
			rs = ps.executeQuery();
			Message m = null;
			while (rs.next()) {
				m = new Message ();
				m.setmid(rs.getString("mid"));
				m.setuserid(rs.getString("userid"));
				m.settitle(rs.getString("title"));
				m.setcontext(rs.getString("context"));
				mL.add(m);
			}
			return mL;
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		} finally {
			JDBC.release(ps,rs,connection);
		}
	}
	
}


